package com.and9.mchen.mcmusic.util;


import java.beans.IntrospectionException;
import java.beans.Introspector;
import java.beans.PropertyDescriptor;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.math.BigInteger;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ColumnListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.apache.commons.lang.ArrayUtils;

import com.and9.mchen.mcmusic.db.DBManager;
import com.and9.mchen.mcmusic.exception.DBException;

/**
 * 数据库查询助手
 * @author Winter Lau<br> 
 */
@SuppressWarnings("unchecked")
public class QueryHelper {
	
	private final static QueryRunner _g_runner = new QueryRunner();
	
	
	private final static ColumnListHandler _g_columnListHandler = new ColumnListHandler(){
		@Override
		protected Object handleRow(ResultSet rs) throws SQLException {
			Object obj = super.handleRow(rs);
			if(obj instanceof BigInteger)
				return ((BigInteger)obj).longValue();
			return obj;
		}
		
	};
	
	private final static ScalarHandler _g_scaleHandler = new ScalarHandler(){
		@Override
		public Object handle(ResultSet rs) throws SQLException {
			Object obj = super.handle(rs);
			if(obj instanceof BigInteger)
				return ((BigInteger)obj).longValue();
			return obj;
		}		
	};
	
	private final static List<Class<?>> PrimitiveClasses = new ArrayList<Class<?>>(){{
		add(Long.class);
		add(Integer.class);
		add(String.class);
		add(java.util.Date.class);
		add(java.sql.Date.class);
		add(java.sql.Timestamp.class);
	}};
	
	private final static boolean _IsPrimitive(Class<?> cls) {
		return cls.isPrimitive() || PrimitiveClasses.contains(cls) ;
	}
	
	/**
	 * 获取数据库连接
	 * @return
	 * @throws DBException 
	 */
	public static Connection getConnection() throws DBException {
		try{
			return DBManager.getConnection();
		}catch(SQLException e){
			throw new DBException(e);
		}
	}

	/**
	 * 读取某个对象
	 * @param sql
	 * @param params
	 * @return
	 * @throws DBException 
	 */
	@SuppressWarnings("rawtypes")
	public static <T> T read(Class<T> beanClass, String sql, Object...params) throws DBException {
		try{
			return (T)_g_runner.query(getConnection(), sql, _IsPrimitive(beanClass)?_g_scaleHandler:new BeanHandler(beanClass), params);
		}catch(SQLException e){
			throw new DBException(e);
		}
	}
	
	
	
	/**
	 * 对象查询
	 * @param <T>
	 * @param beanClass
	 * @param sql
	 * @param params
	 * @return
	 * @throws DBException 
	 */
	@SuppressWarnings("rawtypes")
	public static <T> List<T> query(Class<T> beanClass, String sql, Object...params) throws DBException {
		try{
			return (List<T>)_g_runner.query(getConnection(), sql, _IsPrimitive(beanClass)?_g_columnListHandler:new BeanListHandler(beanClass), params);
		}catch(SQLException e){
			throw new DBException(e);
		}
	}

	
	
	/**
	 * 分页查询
	 * @param <T>
	 * @param beanClass
	 * @param sql
	 * @param page
	 * @param count
	 * @param params
	 * @return
	 * @throws DBException 
	 */
	public static <T> List<T> query_slice(Class<T> beanClass, String sql, int page, int count, Object...params) throws DBException {
		if(page < 0 || count < 0) 
			throw new IllegalArgumentException("Illegal parameter of 'page' or 'count', Must be positive.");
		int from = (page - 1) * count;
		count = (count > 0) ? count : Integer.MAX_VALUE;
		return query(beanClass, sql + " LIMIT ?,?", ArrayUtils.addAll(params, new Integer[]{from,count}));		
	}
	
	
	
	/**
	 * 执行统计查询语句，语句的执行结果必须只返回一个数值
	 * @param sql
	 * @param params
	 * @return
	 * @throws DBException 
	 */
	public static long stat(String sql, Object...params) throws DBException {
		try{
			Number num = (Number)_g_runner.query(getConnection(), sql, _g_scaleHandler, params);
			return (num!=null)?num.longValue():-1;
		}catch(SQLException e){
			throw new DBException(e);
		}
	}

	

	/**
	 * 执行INSERT/UPDATE/DELETE语句
	 * @param sql
	 * @param params
	 * @return
	 * @throws DBException 
	 */
	public static int update(String sql, Object...params) throws DBException {
		try{
			return _g_runner.update(getConnection(), sql, params);
		}catch(SQLException e){
			throw new DBException(e);
		}
	}
	
	/**
	 * 批量执行指定的SQL语句
	 * @param sql
	 * @param params
	 * @return
	 * @throws DBException 
	 */
	public static int[] batch(String sql, Object[][] params) throws DBException {
		try{
			return _g_runner.batch(getConnection(), sql, params);
		}catch(SQLException e){
			throw new DBException(e);
		}
	}
	
	// 获取对象（bean）中的getXXX值
		public static Object[] getFieldsVal(Object bean) {
			// 获得所有的属性
			Field[] fields = null;
			fields = bean.getClass().getDeclaredFields();
			String[] s = new String[fields.length];
			for(int i = 0; i < fields.length; i++){
				s[i] = fields[i].getName();
			}

			return getFieldsVal(bean, s);
		}
		
		public static Object[] getFieldsVal(Object bean, PropertyDescriptor[] properties) {
			Object[] params = new Object[properties.length];
			for (int i = 0; i < properties.length; ++i) {
				PropertyDescriptor property = properties[i];
				Object value = null;
				Method method = property.getReadMethod();
				if (method == null)
					throw new RuntimeException("No read method for bean property "
							+ bean.getClass() + " " + property.getName());

				try {
					value = method.invoke(bean, new Object[0]);
				} catch (InvocationTargetException e) {
					throw new RuntimeException("Couldn't invoke method: " + method,
							e);
				} catch (IllegalArgumentException e) {
					throw new RuntimeException(
							"Couldn't invoke method with 0 arguments: " + method, e);
				} catch (IllegalAccessException e) {
					throw new RuntimeException("Couldn't invoke method: " + method,
							e);
				}
				params[i] = value;
			}

			return params;
		}
		
		public static Object[] getFieldsVal(Object bean, String[] propertyNames) {
			PropertyDescriptor[] descriptors;
		    try
		    {
		      descriptors = Introspector.getBeanInfo(bean.getClass()).getPropertyDescriptors();
		    }
		    catch (IntrospectionException e) {
		      throw new RuntimeException("Couldn't introspect bean " + bean.getClass().toString(), e);
		    }
		    PropertyDescriptor[] sorted = new PropertyDescriptor[propertyNames.length];
		    for (int i = 0; i < propertyNames.length; ++i) {
		      String propertyName = propertyNames[i];
		      if (propertyName == null)
		        throw new NullPointerException("propertyName can't be null: " + i);

		      boolean found = false;
		      for (int j = 0; j < descriptors.length; ++j) {
		        PropertyDescriptor descriptor = descriptors[j];
		        if (propertyName.equals(descriptor.getName())) {
		          sorted[i] = descriptor;
		          found = true;
		          break;
		        }
		      }
		      if (!(found))
		        throw new RuntimeException("Couldn't find bean property: " + bean.getClass() + " " + propertyName);

		    }

			return getFieldsVal(bean, sorted);
		}
		
		//获取对象（bean）中的属性
		public static Field[] getFields(Object obj) {
			return getFields(obj, null);
		}
		
		public static Field[] getFields(Object obj, String[] s) {
			Field[] fields = null;
			Field[] tmp = null;
			Class c = obj.getClass();

			// 获得所有的属性
			tmp = c.getDeclaredFields();

			if(s != null){ // 是否选定部分参数
				fields = new Field[s.length];
				for(int j = 0; j < s.length; j++){
					for(int i = 0; i < tmp.length; i++){
						if(tmp[i].getName().equals(s[j])){
							fields[j] = tmp[i];
						}
					}
				}
			}else{ // 返回全部参数
				fields = tmp;
			}
			
			return fields;
		}

		/**
		 * 得到插入SQL语句， 参数为表名， Bean对象的实例
		 * props为需要插入的字段名数组, 为空时即更新全部
		 * @param tableName
		 * @param obj
		 * @return
		 */
		public static String getInsertSQL(String tableName, Object obj){
			return getInsertSQL(tableName, obj, null);
		}
		public static String getInsertSQL(String tableName, Object obj, String[] props) {
			Field[] fields = null;
			fields = getFields(obj, props);
			
			// 拼SQL语句
			StringBuffer sql = new StringBuffer();
			sql.append("INSERT INTO ");
			sql.append(" " + tableName + " ");
			sql.append(" (");
			for (int i = 0; i < fields.length; i++) {
				sql.append(" " + fields[i].getName() + " ");
				if (i < fields.length - 1) {
					sql.append(",");
				}
			}
			sql.append(") ");
			sql.append(" VALUES(");
			for (int i = 0; i < fields.length; i++) {
				sql.append("?");
				if (i < fields.length - 1) {
					sql.append(",");
				}
			}
			sql.append(") ");
			return sql.toString();
		}

		/**
		 * 得到更新SQL语句， 参数为表名， Bean对象的实例， sWhere为搜索条件， 必须要有搜索条件
		 * props为需要更新的字段名数组, 为空时即更新全部
		 * @param tableName
		 * @param obj
		 * @param sWhere
		 * @return
		 */
		public static String getUpdateSQL(String tableName, Object obj, String sWhere){
			return getUpdateSQL(tableName, obj, sWhere, null);
		}
		
		public static String getUpdateSQL(String tableName, Object obj, String sWhere, String[] props) {
			Field[] fields = null;
			fields = getFields(obj, props);

			// 拼SQL语句
			StringBuffer sql = new StringBuffer();
			sql.append("UPDATE ");
			sql.append(" " + tableName + "  ");
			sql.append(" SET ");
			for (int i = 0; i < fields.length; i++) {
				sql.append(" " + fields[i].getName() + " ");
				sql.append("=?");
				if (i < fields.length - 1) {
					sql.append(",");
				}
			}
			if(sWhere != null && !"".equals(sWhere)){
	        	sql.append(" where ");
	        	sql.append(sWhere);
	        }else{
	        	sql.append(" where 1=2");
	        }
			return sql.toString();
		}

		/**
		 * 得到搜索SQL语句， 参数为表名， sWhere为搜索条件， 必须要有搜索条件
		 * @param tableName
		 * @param sWhere
		 * @return
		 */
		public static String getSelectSQL(String tableName, String sWhere) {
			// 拼SQL语句
			StringBuffer sql = new StringBuffer();
			sql.append("SELECT * FROM ");
			sql.append(" " + tableName + "  ");
			if(sWhere != null && !"".equals(sWhere)){
	        	sql.append(" where ");
	        	sql.append(sWhere);
	        }
			return sql.toString();
		}

		/**
		 * 得到删除SQL语句， 参数为表名， sWhere为搜索条件， 必须要有搜索条件
		 * @param tableName
		 * @param sWhere
		 * @return
		 */
		public static String getDeleteSQL(String tableName, String sWhere) {
			// 拼SQL语句
			StringBuffer sql = new StringBuffer();
			sql.append("DELETE FROM ");
			sql.append(" " + tableName + "  ");
			if(sWhere != null && !"".equals(sWhere)){
	        	sql.append(" where ");
	        	sql.append(sWhere);
	        }else{
	        	sql.append(" where 1=2");
	        }

			return sql.toString();
		}
	
	
	public static String getInsertSQLByProperty(String tableName, String... proertyNames){
		StringBuffer sql=new StringBuffer();
		sql.append("INSERT INTO ");
		sql.append(" " + tableName + " ");
		sql.append(" (");
		for (int i = 0; i < proertyNames.length; i++) {
			sql.append(" " + proertyNames[i] + " ");
			if (i < proertyNames.length - 1) {
				sql.append(",");
			}
		}
		sql.append(") ");
		sql.append(" VALUES(");
		for (int i = 0; i < proertyNames.length; i++) {
			sql.append("?");
			if (i < proertyNames.length - 1) {
				sql.append(",");
			}
		}
		sql.append(") ");
		return sql.toString();
	}
	
	public static String getUpdateSQLByProperty(String tableName, String sWhere, String... props) {
		// 拼SQL语句
		StringBuffer sql = new StringBuffer();
		sql.append("UPDATE ");
		sql.append(" " + tableName + "  ");
		sql.append(" SET ");
		for (int i = 0; i < props.length; i++) {
			sql.append(" " + props[i] + " ");
			sql.append("=?");
			if (i < props.length - 1) {
				sql.append(",");
			}
		}
		if(sWhere != null && !"".equals(sWhere)){
        	sql.append(" where ");
        	sql.append(sWhere);
        }else{
        	sql.append(" where 1=2");
        }
		return sql.toString();
	}

}
